package com.hahehiho.myalbum;

import java.io.File;
import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Iterator;
import java.util.Map;

public class MyAlbumDB {
	private static MyAlbumDB instance;

	private MyAlbumDB() {
		try {
			Class.forName("com.mysql.jdbc.Driver").newInstance();
		} catch (InstantiationException e) {
			e.printStackTrace();
		} catch (IllegalAccessException e) {
			e.printStackTrace();
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		}
	}

	synchronized public static MyAlbumDB getInstance() {
		if (instance == null)
			instance = new MyAlbumDB();

		return instance;
	}

	public Connection getMyConnection() throws SQLException {
		return DriverManager.getConnection(
//				"jdbc:mysql://192.168.0.30:3306/myalbum", "hahehiho", "2417bg");
				"jdbc:mysql://hahehiho.iptime.org:3306/myalbum?useUnicode=true&characterEncoding=utf8", "hahehiho", "2417bg");
	}
	
	public MyAlbumVO selectPhoto(String pKey) {
		MyAlbumVO vo = new MyAlbumVO();
		
		Connection conn = null;
		try {
			conn = getMyConnection();
		} catch (SQLException ex) {
			ex.printStackTrace();
		}
		
		/* delete */
		Statement stmt = null;
		try {
			stmt = conn.createStatement();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		
		String query = "select " + "PKEY, ORG_DATE, FILE_PATH, LATITUDE, LONGITUDE, FULL_ADDR, XPTITLE, XPSUBJECT, XPKEYWORDS, XPCOMMENT"; 
		query += " from ALBUM ";
		query += " where PKEY=" + pKey;
		
		ResultSet result = null;
		try {
			result = stmt.executeQuery(query);
			System.out.println("[DBG]" + query);

			while (result.next()) {
				vo.setpKey(Integer.valueOf(result.getString("PKEY")));
				
				Calendar c = Calendar.getInstance();
				c.setTime(result.getTimestamp("ORG_DATE"));				
				vo.setOrgDate(c);

				vo.setFilePath(result.getString("FILE_PATH"));
				
				vo.setLatitude(result.getDouble("LATITUDE"));
				vo.setLongitude(result.getDouble("LONGITUDE"));
				vo.setFullAddr(result.getString("FULL_ADDR"));
				vo.setXpTitle(result.getString("XPTITLE"));
				vo.setXpSubject(result.getString("XPSUBJECT"));
				vo.setXpKeywords(result.getString("XPKEYWORDS"));
				vo.setXpComment(result.getString("XPCOMMENT"));
			}
		} catch (SQLException e1) {
			// TODO Auto-generated catch block
			e1.printStackTrace();
		}

		
		try {
			conn.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		return vo;
	}

	public void recreateMyAlbum(Map<String, MyAlbumVO> items, String thumbnail, boolean isSkipExist, RecreateInfoStatus info, boolean isDB) throws IOException {
		Connection conn = null;
		Statement stmt = null;
		PreparedStatement pstmt = null;
		
		if(isDB) {
			try {
				conn = getMyConnection();
			} catch (SQLException ex) {
				ex.printStackTrace();
			}

			/* delete */
			try {
				stmt = conn.createStatement();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}

			try {
				stmt.execute("delete from ALBUM");
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}

			try {
				String insertQuery = "insert into ALBUM(ORG_DATE,ORG_DATE_YEAR,ORG_DATE_MONTH,ORG_DATE_DAY,"
						+"FILE_PATH,"
						+"LATITUDE,LONGITUDE,"
						+"ADDR1,ADDR2,ADDR3,ADDR4,ADDR5,ADDR6,ADDR7,ADDR8,ADDR9,"
						+"FULL_ADDR,"
						+"XPTITLE,XPSUBJECT,XPKEYWORDS,XPCOMMENT"
						+ ")" 
						+ " values(?,?,?,?" + ",?" + ",?,?" + ",?,?,?,?,?,?,?,?,?"+ ",?" + ",?,?,?,?"+ ")";

				pstmt = conn.prepareStatement(insertQuery);
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}

		int totalSize = items.size();
		int count = 1;
		
		/* insert */
		Iterator<String> it = items.keySet().iterator();
		while (it.hasNext()) {
			String filePath = it.next();
			MyAlbumVO metadata = items.get(filePath);

			GPS gps = null;
			if(isDB) {
				gps = new GPS(metadata);
			}

			try {
				if(isDB) {
					Calendar c = metadata.getOrgDate();
					pstmt.setTimestamp(1, new Timestamp(c.getTime().getTime()));

					pstmt.setInt(2, c.get(Calendar.YEAR));
					pstmt.setInt(3, c.get(Calendar.MONTH) + 1);
					pstmt.setInt(4, c.get(Calendar.DAY_OF_MONTH));
					pstmt.setString(5, filePath);
					pstmt.setDouble(6, gps.getLatitude());
					pstmt.setDouble(7, gps.getLongitude());

					int gpsStartIndex = 8;
					for(int i = 0; i < gps.getAddress().length; i++) {
						pstmt.setString(gpsStartIndex+i, gps.getAddress()[i]);
					}
					pstmt.setString(gpsStartIndex+gps.getAddress().length, gps.getFullAddr());
					
					int insertIndex = gpsStartIndex+gps.getAddress().length;
					pstmt.setString(insertIndex+1, metadata.getXpTitle());
					pstmt.setString(insertIndex+2, metadata.getXpSubject());
					pstmt.setString(insertIndex+3, metadata.getXpKeywords());
					pstmt.setString(insertIndex+4, metadata.getXpComment());

					pstmt.executeUpdate();
				}
				
				if(thumbnail.equalsIgnoreCase("true")) {
					try {
						Thumbnail.createImage(filePath, filePath.replace("MGTEC/Picture", "MGTEC/Picture.t"), isSkipExist);
					} catch (IOException e) {
						e.printStackTrace();
					}
				}
				

			} catch (SQLException e) {
				e.printStackTrace();
			}
			
			count++;
			info.setPercentage(((count*100)/totalSize));
		}

		if(isDB) {
			try {
				conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		
		try {
			Runtime.getRuntime().exec("chown -R pi:pi /media/MGTEC/Picture.t/");
			Runtime.getRuntime().exec("chmod -R 770 /media/MGTEC/Picture.t/");
		} catch (IOException e) {
			e.printStackTrace();
		}
	}

	public MyAlbumVO[] selectPhotoListByGPS(String[] addrs, String isGroup) {
		Connection conn = null;
		try {
			conn = getMyConnection();
		} catch (SQLException ex) {
			ex.printStackTrace();
		}
		
		/* delete */
		Statement stmt = null;
		try {
			stmt = conn.createStatement();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		if(addrs == null)
			addrs = new String[0];
		

		String[] columns = new String[]{"ADDR1", "ADDR2", "ADDR3", "ADDR4", "ADDR5", "ADDR6", "ADDR7", "ADDR8", "ADDR9"};
		
		
		String columnQuery = "";
		for (int i = 0; i < (addrs.length+1); i++) {
			columnQuery += columns[i];
			if(i != (addrs.length))
				columnQuery += ",";
		}
		
		String query = "select ";
		query += columnQuery + ", FILE_PATH, ORG_DATE, FULL_ADDR, PKEY ";
		if(isGroup.equalsIgnoreCase("true")) {
			query += " , count(*) as FILE_COUNT ";
		}
		
		query += " from ALBUM where (1 = 1) ";
		
		for (int i = 0; i < addrs.length; i++) {
			query += "and (" + columns[i] + " like ('"+ addrs[i] +"')) ";
		}
		

		if(isGroup.equalsIgnoreCase("true")) {
			query += "group by ";		
			query += columnQuery;
		}
		
		query += " order by ORG_DATE";
		
		ArrayList<MyAlbumVO> alist = new ArrayList<MyAlbumVO>();
		
		ResultSet result = null;
		try {
			System.out.println("[DBG]" + query);
			result = stmt.executeQuery(query);

			while (result.next()) {
				MyAlbumVO vo = new MyAlbumVO();
				
				Calendar c = Calendar.getInstance();
				c.setTime(result.getTimestamp("ORG_DATE"));
				
				vo.setOrgDate(c);
				if(isGroup.equalsIgnoreCase("false"))
					vo.setCount(1);
				else
					vo.setCount(result.getInt("FILE_COUNT"));

				String resultAddrs[] = new String[addrs.length+1];
				for (int i = 0; i < (addrs.length+1); i++) {
					resultAddrs[i] = result.getString(columns[i]);
				}
				vo.setAddrs(resultAddrs);
				
				vo.setFilePath(result.getString("FILE_PATH"));
				
				vo.setFullAddr(result.getString("FULL_ADDR"));

				vo.setpKey(Integer.valueOf(result.getString("PKEY")));

				alist.add(vo);
			}
		} catch (SQLException e1) {
			// TODO Auto-generated catch block
			e1.printStackTrace();
		}

		
		try {
			conn.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		return alist.toArray(new MyAlbumVO[0]);		
	}
	
	
	public MyAlbumVO[] selectPhotoList(String depth, String year, String month, String day, String isGroup) {
		Connection conn = null;
		try {
			conn = getMyConnection();
		} catch (SQLException ex) {
			ex.printStackTrace();
		}
		
		/* delete */
		Statement stmt = null;
		try {
			stmt = conn.createStatement();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		if(depth.equalsIgnoreCase("detail")) {
			isGroup = "false";
		}
		
		String[] columns = null;
		if(depth.equalsIgnoreCase("year"))
			columns = new String[]{ "ORG_DATE_YEAR"};
		else if(depth.equalsIgnoreCase("month"))
			columns = new String[]{ "ORG_DATE_YEAR", "ORG_DATE_MONTH"};
		else if(depth.equalsIgnoreCase("day") || depth.equalsIgnoreCase("detail"))
			columns = new String[]{ "ORG_DATE_YEAR", "ORG_DATE_MONTH", "ORG_DATE_DAY"};
		
		String columnQuery = "";
		for (int i = 0; i < columns.length; i++) {
			columnQuery += columns[i];
			if(i != (columns.length-1))
				columnQuery += ",";
		}
		
		String query = "select ";
		query += columnQuery + ", FILE_PATH, ORG_DATE, PKEY ";
		if(isGroup.equalsIgnoreCase("true"))
			query += " , count(*) as FILE_COUNT ";
		
		query += " from ALBUM ";
		if (!depth.equalsIgnoreCase("year")) {
			query += " where ";
			if(year != null && !year.isEmpty())
				query += "(ORG_DATE_YEAR = " + year + ") ";
			if(month != null && !month.isEmpty())
				query += "AND (ORG_DATE_MONTH = " + month + ") ";
			if(day != null && !day.isEmpty())
				query += "AND (ORG_DATE_DAY = " + day + ") ";
		}
		if(isGroup.equalsIgnoreCase("true")) {
			query += "group by ";		
			query += columnQuery;
		}
		
		query += " order by ORG_DATE";
		
		ArrayList<MyAlbumVO> alist = new ArrayList<MyAlbumVO>();
		
		ResultSet result = null;
		try {
			result = stmt.executeQuery(query);
			System.out.println("[DBG]" + query);

			while (result.next()) {
				MyAlbumVO vo = new MyAlbumVO();
				vo.setOrgDateYear(result.getInt("ORG_DATE_YEAR"));
				
				Calendar c = Calendar.getInstance();
				c.setTime(result.getTimestamp("ORG_DATE"));
				
				vo.setOrgDate(c);
				if(depth.equalsIgnoreCase("detail") || isGroup.equalsIgnoreCase("false"))
					vo.setCount(1);
				else
					vo.setCount(result.getInt("FILE_COUNT"));
					
				if (!depth.equalsIgnoreCase("year")) {
					vo.setOrgDateMonth(result.getInt("ORG_DATE_MONTH"));
					if (depth.equalsIgnoreCase("day") || depth.equalsIgnoreCase("detail")) {
						vo.setOrgDateDay(result.getInt("ORG_DATE_DAY"));
					}
				}
				vo.setFilePath(result.getString("FILE_PATH"));
				vo.setpKey(Integer.valueOf(result.getString("PKEY")));
				alist.add(vo);
			}
		} catch (SQLException e1) {
			// TODO Auto-generated catch block
			e1.printStackTrace();
		}

		
		try {
			conn.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		return alist.toArray(new MyAlbumVO[0]);
	}


	public MyAlbumVO[] selectAllPhotoList() {
		Connection conn = null;
		try {
			conn = getMyConnection();
		} catch (SQLException ex) {
			ex.printStackTrace();
		}
		
		/* delete */
		Statement stmt = null;
		try {
			stmt = conn.createStatement();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		String query = "select ORG_DATE_YEAR, ORG_DATE_MONTH, ORG_DATE_DAY, ORG_DATE, FILE_PATH, PKEY from ALBUM";
		
		ArrayList<MyAlbumVO> alist = new ArrayList<MyAlbumVO>();

		ResultSet result = null;
		try {
			result = stmt.executeQuery(query);
			System.out.println("[DBG]" + query);

			while (result.next()) {
				MyAlbumVO vo = new MyAlbumVO();
				vo.setOrgDateYear(result.getInt("ORG_DATE_YEAR"));
				
				Calendar c = Calendar.getInstance();
				c.setTime(result.getTimestamp("ORG_DATE"));
				
				vo.setOrgDate(c);
				vo.setOrgDateMonth(result.getInt("ORG_DATE_MONTH"));
				vo.setOrgDateDay(result.getInt("ORG_DATE_DAY"));
				vo.setFilePath(result.getString("FILE_PATH"));
				
				vo.setpKey(Integer.valueOf(result.getString("PKEY")));

				alist.add(vo);
			}
		} catch (SQLException e1) {
			// TODO Auto-generated catch block
			e1.printStackTrace();
		}
		
		try {
			conn.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		return alist.toArray(new MyAlbumVO[0]);
	}
	
	public boolean updatePhoto(MyAlbumVO vo) {
		Connection conn = null;
		PreparedStatement pstmt = null;
		boolean isSuccess = false;
		
		try {
			conn = getMyConnection();
		} catch (SQLException ex) {
			ex.printStackTrace();
			
			return isSuccess;
		}

		GPS gps = null;
		try {
			gps = new GPS(vo);
		} catch (IOException e1) {
			e1.printStackTrace();
		}
		
		/* modify file */
		if(!ImageFileHandler.modifyMyAlbumVO(vo))
			return false;

		try {
			String updateQuery = "update ALBUM set ORG_DATE = ?, ORG_DATE_YEAR = ?, ORG_DATE_MONTH = ?, ORG_DATE_DAY = ?,"
					+"LATITUDE = ?,LONGITUDE = ?,"
					+"ADDR1 = ?,ADDR2 = ?,ADDR3 = ?,ADDR4 = ?,ADDR5 = ?,ADDR6 = ?,ADDR7 = ?,ADDR8 = ?,ADDR9 = ?,"
					+"FULL_ADDR = ?,"
					+"XPTITLE = ?,XPSUBJECT = ?,XPKEYWORDS = ?,XPCOMMENT = ? where pkey = " + vo.getpKey();

			pstmt = conn.prepareStatement(updateQuery);

			Calendar c = vo.getOrgDate();
			pstmt.setTimestamp(1, new Timestamp(c.getTime().getTime()));

			pstmt.setInt(2, c.get(Calendar.YEAR));
			pstmt.setInt(3, c.get(Calendar.MONTH) + 1);
			pstmt.setInt(4, c.get(Calendar.DAY_OF_MONTH));
			pstmt.setDouble(5, gps.getLatitude());
			pstmt.setDouble(6, gps.getLongitude());

			int gpsStartIndex = 7;
			for(int i = 0; i < gps.getAddress().length; i++) {
				pstmt.setString(gpsStartIndex+i, gps.getAddress()[i]);
			}
			pstmt.setString(gpsStartIndex+gps.getAddress().length, gps.getFullAddr());

			int insertIndex = gpsStartIndex+gps.getAddress().length;
			pstmt.setString(insertIndex+1, vo.getXpTitle());
			pstmt.setString(insertIndex+2, vo.getXpSubject());
			pstmt.setString(insertIndex+3, vo.getXpKeywords());
			pstmt.setString(insertIndex+4, vo.getXpComment());
			

			if(pstmt.executeUpdate() == 1)
				isSuccess = true;
			
		} catch (SQLException e) {
			e.printStackTrace();
		}


		try {
			conn.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		
		return isSuccess;
	}

	public static void main(String[] args) throws IOException {
		long startTime = System.currentTimeMillis();

		Indexer indexer = new Indexer(new File("C:\\Users\\hahehiho\\Pictures"));
		indexer.createIndex();

		MyAlbumDB db = MyAlbumDB.getInstance();
		db.recreateMyAlbum(indexer.getItems(), "false", true, Util.getWorker(), true);

		long endTime = System.currentTimeMillis();

		long eltime = endTime - startTime;

		System.out.println("total time : " + eltime / 1000 + "sec " + eltime
				% 1000 + "msec");
	}

	public GPSCacheVO selectGPSCache(double latitude, double longitude) {
		Connection conn = null;
		try {
			conn = getMyConnection();
		} catch (SQLException ex) {
			ex.printStackTrace();
		}
		
		Statement stmt = null;
		try {
			stmt = conn.createStatement();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		
		String query = "select LATITUDE, LONGITUDE, FULL_ADDR from GPS_CACHE where LATITUDE = " + latitude + " AND LONGITUDE = " + longitude;
		
		GPSCacheVO vo = null;
		ResultSet result = null;
		try {
			result = stmt.executeQuery(query);
			System.out.println("[DBG]" + query);
			
			if(result.next()) {
				vo = new GPSCacheVO();
				vo.setLatitude(result.getDouble("LATITUDE"));
				vo.setLongitude(result.getDouble("LONGITUDE"));
				vo.setFullAddr(result.getString("FULL_ADDR"));
			}
		} catch (SQLException e1) {
			e1.printStackTrace();
		}
		
		try {
			conn.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		
		return vo;
	}
	
	public void insertGPSCache(GPSCacheVO vo) {
		Connection conn = null;
		try {
			conn = getMyConnection();
		} catch (SQLException ex) {
			ex.printStackTrace();
		}
		

		String query = "insert into GPS_CACHE(LATITUDE, LONGITUDE, FULL_ADDR) values(?,?,?)";
		
		PreparedStatement pstmt = null;
		try {
			pstmt = conn.prepareStatement(query);
		} catch (SQLException e2) {
			// TODO Auto-generated catch block
			e2.printStackTrace();
		}
		
		try {
			pstmt.setDouble(1, vo.getLatitude());
			pstmt.setDouble(2, vo.getLongitude());
			pstmt.setString(3, vo.getFullAddr());
			pstmt.executeUpdate();
		} catch (SQLException e2) {
			e2.printStackTrace();
		}
		
		try {
			pstmt.close();
			conn.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

}
